package com.neusoft.common;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.neusoft.common.db.BaseDAO;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/**
 * 生成Excel
 *2009-11-17
 */
public class CreateExcel extends BaseDAO {
	


	
	
	/**
	 * 入口方法 <b>参数不明白的看最底下</b>
	 * @param os 输出流
	 * @param sql 查询语句
	 * @param titleList 头的“Label”集合 头包括：表格大标题  和每列的标题 
	 * @param footList 脚的“Label”集合
	 * @param sumCall 需要统计的列
	 * @param mergeCellList 合并列的坐标集合
	 */
	public void createExcelFile(OutputStream os ,String sql ,List<String> titleList ,List<String> footList ,Map<Integer,Double> sumCall ) {
		
		WritableWorkbook wwb = null;
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try {
			//Class.forName("oracle.jdbc.driver.OracleDriver");
			//Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.100:1521:qiezi", "qiezi","qiezi");			
			
			conn=this.jdbcTemplate.getDataSource().getConnection();
			
			stmt = conn.createStatement();		

			rs = stmt.executeQuery(sql);

			wwb = Workbook.createWorkbook(os);

			jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 0);

//			if(mergeCellList!=null){
//				// 合并单元格  合并标题				
//				for(int i=0;i<mergeCellList.size();i++){					
//					int[] mergeCell=mergeCellList.get(i);
//					ws.mergeCells(mergeCell[0], mergeCell[1], mergeCell[2], mergeCell[3]);	
//				}
//							
//			}

			if(titleList!=null){	
				// 合并单元格  合并标题	
				ws.mergeCells(0,0,titleList.size()-3,0);	
				ws.mergeCells(0,1,titleList.size()-3,1);	
				
				// 添加表头
				for(int i=0;i<titleList.size();i++){
					if(i==0){
						ws.addCell(new Label(0, 0, titleList.get(i).toString(),CreateExcel.getHeader()));
					}else if(i==1){
						ws.addCell(new Label(0, 1,titleList.get(i).toString(),CreateExcel.getHeader_Right()));
					}else{
						ws.addCell(new Label(i-2, 2,titleList.get(i).toString(),CreateExcel.getTitle()));
					}
					
				}
				
			}			
			
			// 填充数据
			ResultSetMetaData msmd = rs.getMetaData();
			int callNumber = msmd.getColumnCount();
			int rowNumber = ws.getRows();
			while (rs.next()) {				
				//设置列宽度
				for(int i=0;i<callNumber;i++){
					ws.setColumnView(i, 20);
				}		
				//真正填充
				for (int e = 0; e < callNumber; e++) {
					String callStr = rs.getString(e + 1);
					Label label = new Label(e, rowNumber, callStr,getNormolCell());
					ws.addCell(label);
					//累加					
					if (sumCall!=null&&sumCall.get(e + 1) != null) {
						Double number = Double.parseDouble(sumCall.get(e + 1).toString());
						number += Double.parseDouble(callStr != null ? callStr: "0");
						sumCall.put(e + 1, number);
					}
				}
				rowNumber++;
			}			
			
			// 添加统计			
			if(sumCall!=null&&sumCall.size()>0&&footList!=null){
				Set set=sumCall.keySet();			
				Object[] keyList=set.toArray();	
				int keyIndex=0;				
				for(int i=0;i<footList.size();i++){					
					String[] param=footList.get(i).split(",");					
					int call=Integer.parseInt(param[0]);		
					String str=param[1]==null||param[1].equals("")||param[1].toLowerCase().equals("null")?null:param[1];	
					
					if(str==null||"".equals(str)){
						ws.addCell(new Label(call,rowNumber,sumCall.get(keyList[keyIndex]).toString(),CreateExcel.getTitle()));
						keyIndex++;
					}else{
						ws.addCell(new Label(call,rowNumber,str,CreateExcel.getTitle()));						
					}						
				}				
			}else if(footList!=null){
				for(int i=0;i<footList.size();i++){
					String[] param=footList.get(i).split(",");					
					int call=Integer.parseInt(param[0]);		
					String str=param[1]==null||param[1].equals("")||param[1].toLowerCase().equals("null")?null:param[1];
					ws.addCell(new Label(call,rowNumber,str,CreateExcel.getTitle()));
				}
					
			}
		} catch (Exception e) {
				e.printStackTrace();
		} finally {
			if (wwb != null) {
				try {
					wwb.write();
					wwb.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			try {
				if(rs!=null){rs.close();}				
				if(stmt!=null){	stmt.close();}				
				if(conn!=null){	conn.close();}			
			} catch (SQLException e) {
				e.printStackTrace();
			}	
		}
	}

	/**
	 * 设置头的样式
	 * 
	 * @return
	 */
	private static WritableCellFormat getHeader() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 24,
				WritableFont.BOLD);// 定义字体
		try {
			font.setColour(Colour.BLACK);// 字体颜色
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			//format.setBackground(Colour.YELLOW);// 黄色背景
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}
	/**
	 * 设置头的样式_右边对齐
	 * 
	 * @return
	 */
	private static WritableCellFormat getHeader_Right() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 12,
				WritableFont.BOLD);// 定义字体
		try {
			font.setColour(Colour.BLACK);// 字体颜色
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.RIGHT);// 右对齐
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			//format.setBackground(Colour.YELLOW);// 黄色背景
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}
	
	/** */
	/**
	 * 设置标题样式
	 * 
	 * @return
	 */
	private static WritableCellFormat getTitle() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 12,WritableFont.BOLD);
		try {
			font.setColour(Colour.BLACK);// 字体颜色
			
		} catch (WriteException e1) {
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);

		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	/** */
	/**
	 * 设置其他单元格样式
	 * 
	 * @return
	 */
	private static WritableCellFormat getNormolCell() {// 12号字体,上下左右居中,带黑色边框
		WritableFont font = new WritableFont(WritableFont.TIMES, 12);
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}	
}
